package ai.dao;

import ai.bussness.DBOperation;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

/**
 * Created by 杨济瑞 on 2016/8/10.
 */
public class Insert {

    Connection conn;
    PreparedStatement ps;
    DBOperation dbOperation = new DBOperation();
    ResultSet rs;
    Serializable ret;

    /**
     * 添加会员的信息
     *
     * @param name    姓名
     * @param address 地址
     * @param job     职业
     * @return 当前操作的记录编号
     */
    public Serializable addMember(String name, String address, String job) {

        String sql = "insert into member values (null,?,?,?)";

        try {
            conn = dbOperation.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, address);
            ps.setString(3, job);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()){
                ret = (Serializable) rs.getObject(1);
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            try {
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ret;
    }


    /**
     * 保存年卡的数据
     *  @param name 姓名
     * @param type 年卡类型
     * @return 当前操作的记录编号
     */
    public Serializable addCard(String name, String type) {
        String sql = "insert into card values (null,?,?,?)";
        try {

            //设定卡日期
            SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");
            Calendar calendar = Calendar.getInstance();
            String startTime = sFormat.format(calendar.getTime());
            calendar.add(Calendar.YEAR, +1);
            String endTime = sFormat.format(calendar.getTime());
            java.sql.Date startDate = java.sql.Date.valueOf(startTime);
            java.sql.Date endDate = java.sql.Date.valueOf(endTime);

            conn = dbOperation.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, -1);
            ps.setObject(2, startDate);
            ps.setObject(3, endDate);
            ps.executeUpdate();

            rs = ps.getGeneratedKeys();
            if (rs.next()){
                ret = (Serializable) rs.getObject(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 保存计数卡的信息
     * @param name 姓名
     * @param type 类型
     * @param count 计数次数
     * @return 当前操作的记录编号
     */
    public Serializable addCard(String name,String type,int count){
        String sql = "insert into card values (null,?,?,?)";
        try {

            SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");
            Calendar calendar = Calendar.getInstance();
            String startTime = sFormat.format(calendar.getTime());
            calendar.add(Calendar.YEAR, +1);
            String endTime = sFormat.format(calendar.getTime());
            java.sql.Date startDate=java.sql.Date.valueOf(startTime);
            java.sql.Date endDate=java.sql.Date.valueOf(endTime);

            conn = dbOperation.getConnection();

            ps = conn.prepareStatement(sql);
            ps.setInt(1, count);
            ps.setObject(2, startDate);
            ps.setObject(3, endDate);
            ps.executeUpdate();

            rs = ps.getGeneratedKeys();
            if (rs.next()){
                ret = (Serializable) rs.getObject(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return ret;
    }

    /**
     * 添加卡和人对应关系
     * @param memberId
     * @param cardId
     */
    public void addMemberCard(int memberId,int cardId){
        String sql = "insert into MEMBERCARD values (null,?,?)";
        try {
            conn = dbOperation.getConnection();

            ps = conn.prepareStatement(sql);
            ps.setInt(1, memberId);
            ps.setInt(2, cardId);
            ps.executeUpdate();

            rs = ps.getGeneratedKeys();
            if (rs.next()){
                ret = (Serializable) rs.getObject(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加课程
     * @param course 名称
     */
    public void addCourse(String course){
        String sql = "insert into COURSE values (null,?,?,?)";
        try {
            conn = dbOperation.getConnection();

            ps = conn.prepareStatement(sql);
            ps.setString(1, course);
            ps.setInt(2, 10);
            ps.setInt(3, 1);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    /**
     * 添加教练信息
     * @param name
     * @param age
     * @param family
     * @param advantage
     */
    public void addCoach(String name,int age,String family,String advantage){
        String sql = "insert into COACH values (null,?,?,?,?)";
        try{
            conn = dbOperation.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setInt(2, age);
            ps.setString(3, family);
            ps.setString(4,advantage);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
